package com.sushe.dao.impl;

import com.sushe.dao.TeacherDao;
import com.sushe.domain.teacher;
import com.sushe.utils.JDBCUtils;
import com.sushe.utils.TestUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author PanYue
 * @createTime 2022-04-06 14:36
 * 示例
 * 楼宇管理员实现类
 */
public class TeacherDaoImpl implements TeacherDao {
    private Connection conn;
    private PreparedStatement ps;
    private ResultSet rs;

    public List<teacher> selectAll() {
        String sql="SELECT Teacher_Name,Teacher_Sex,Teacher_Tel,Teacher_Username FROM teacher";
        List<teacher> list = new ArrayList<teacher>();
        try {
            conn= JDBCUtils.getconnection();
            ps=conn.prepareStatement(sql);
            getTeacher(list);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.close(rs,ps,conn);
        }
        return list;
    }
    public List<teacher> selectLike(teacher teacher) {
        String condition="";
        String connect=
        TestUtils.isEmpty(condition=teacher.getTeaName())?"Teacher_Name":
        TestUtils.isEmpty(condition=teacher.getUserName())?"Teacher_Username":
        TestUtils.isEmpty(condition=teacher.getTel())?"Teacher_Tel":"";
        connect=TestUtils.isEmpty(connect)?"where "+connect+" like ?":"";
        String sql="SELECT Teacher_Name,Teacher_Sex,Teacher_Tel,Teacher_Username FROM teacher "+connect;
        System.out.println(sql);
        List<teacher> list = new ArrayList<teacher>();
        try {
            conn= JDBCUtils.getconnection();
            ps=conn.prepareStatement(sql);
            ps.setString(1,'%'+condition+'%');
            getTeacher(list);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.close(rs,ps,conn);
        }
        return list;
    }
    public void deleteOne(int id) {
        //id 为1表示已离职
        if(id==1)return;
        //log表和tb表分别都有teacher的外键约束
        //解决方法：
        //将log 和 tb 对应的teacher外键改为1 然后在删除

        //将log表的log_teacherID = TeacherID 的值改为1
        String uplog="UPDATE log SET Log_TeacherID=1 where Log_TeacherID=?";
        //将tb表的TB_TeacherID = TeacherID 的值改为1
        String uptb="UPDATE tb SET TB_TeacherID=1 where TB_TeacherID=?";
        //最后在删除teacher表的记录
        String sql="DELETE FROM teacher WHERE Teacher_ID=?";
        String dellog="DELETE FROM teacher t,log l where t.eacher_ID=.id";
        try {
            conn=JDBCUtils.getconnection();
            ps=conn.prepareStatement(uplog);
            ps.setInt(1,id);
            ps.executeUpdate();

            ps=conn.prepareStatement(uptb);
            ps.setInt(1,id);
            ps.executeUpdate();

            ps=conn.prepareStatement(sql);
            ps.setInt(1,id);
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    public void updateOne(teacher teacher) {
        String sql="UPDATE teacher SET Teacher_Username=?,Teacher_Password=?,Teacher_Name=?,Teacher_Sex=?,Teacher_Tel=? WHERE Teacher_ID=?";
        try {
            update(teacher, sql);
            ps.setInt(6,teacher.getId());
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void insertOne(teacher teacher) {
        String sql="insert into teacher values(null,?,?,?,?,?)";
        try {
            update(teacher, sql);
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private void update(teacher teacher, String sql) throws SQLException {
        conn= JDBCUtils.getconnection();
        ps=conn.prepareStatement(sql);
        ps.setString(1,teacher.getUserName());
        ps.setString(2,teacher.getPassword());
        ps.setString(3,teacher.getTeaName());
        ps.setString(4,teacher.getSex());
        ps.setString(5,teacher.getTel());
    }

    private void getTeacher(List<teacher> list) throws SQLException {
        rs=ps.executeQuery();
        while (rs.next()){
            teacher teachers = new teacher();
            teachers.setTeaName(rs.getString("Teacher_Name"));
            teachers.setSex(rs.getString("Teacher_Sex"));
            teachers.setTel(rs.getString("Teacher_Tel"));
            teachers.setUserName(rs.getString("Teacher_Username"));
            list.add(teachers);
        }
    }
}
